import pandas as pandas
#import xlrd
#xls = xlrd.open_workbook(r'Stock_Data.xlsx', on_demand=True)
#print xls.sheet_names()

data = pandas.read_excel('Stock_Data.xlsx', 'AmericanExchanges')
import copy
data_backup=copy.deepcopy(data)

data['Dates']=pandas.to_datetime(data['Names Date'].values, format = '%Y%m%d')

data = data[pandas.notnull(data['Company Name'])]

pcosset = set(data['PERMCO'].values)
pcoslist = list(set(data['PERMCO'].values))
# Make sure no Permco's are repeated
assert len(pcosset) ==len(pcoslist)

import csv
with open('w_pco.csv', 'wb') as csvfile:
    w = csv.writer(csvfile, delimiter=' ')
    for p in pcosset:
        cpnies = list(set(data['Company Name'][data['PERMCO']==p]))
        row = str(p) + ', '+ ', '.join(cpnies) 
        w.writerow(row)

for p in pcosset:
    cpnies = list(set(data['Company Name'][data['PERMCO']==p]))
    row = str(p) + ', '+ ', '.join(cpnies) 
    fig = plt.figure()
    fig.set_size_inches(16,4)
    to_plot =data[data['PERMCO'] == p]
    to_plot.plot('Dates', 'Price or Bid/Ask Average', subplots=True, title =row)
    plt.show()
    plt.close()